9.4 Подключение к таблице на листе Excel

Подключение к таблице Excel средствами ADO, создание именованного диапазона, создание источника данных ODBC

Очень часто в практической работе возникает необходимость подключиться к таблице на листе Excel, как к базе данных. Конечно, можно работать и средствами объектной модели Excel (см. раздел 11 "Программирование в Excel"), но использование объектов ADO дает значительные преимущества:

• намного проще и удобнее производить поиск записи, вставку новых записей в таблицу, изменение существующих записей. Объекты ADO изначально проектировались именно для этих целей;
• объектную модель Excel можно использовать только в Excel, а объекты ADO универсальны и могут использоваться для подключения к любым источникам данных. Если вы используете объекты ADO, то вы можете использовать фактически одно и то же приложение как для работы с данными в Excel, так и для работы с информацией в "большой" базе данных - например, SQL Server или Oracle. Ситуация, когда часть информации находится в базе данных, а другая часть - в книге Excel, встречается на практике очень часто.

Подключиться к таблице на листе Excel совсем не сложно, но самостоятельно догадаться до всей последовательности действий бывает трудно. Поэтому ниже приведена пошаговая последовательность действий.

Предположим, что у нас есть книга Excel, которая называется Fact.xls и лежит в корневом каталоге диска C:. На первом листе этой книге есть такая совсем несложная таблица, представленная на рис. 9.7

Рис. 9.7 Таблица в Excel, к которой нужно обратиться средствами ADO

Нам необходимо подключиться к этой таблице, как к базе данных. Что нам нужно сделать?

Первый этап - это подготовка. Иногда можно обойтись и без нее (если лист Excel - это одна таблица). На практике же часто бывает так, что на листе у нас несколько таблиц, или таблица с комментариями, или внизу таблицы посчитаны итоги и т.п. Чтобы не смущать Excel, лучше явно указать нашу таблицу. Сделать это очень просто: нужно ее выделить (в нашем случае - выделить диапазон с B3 по D8) и присвоить выделенному диапазону имя. Для этого в Excel в меню Вставка нужно выбрать Имя -> Присвоить и ввести нужное имя. В нашем случае мы присвоим имя Volumes (см. рис. 9.8)

Рис. 9.8 Присваиваем имя диапазону

Обратите внимание, что нужно выбирать диапазон вместе с названиями столбцов.

После того, как имя присвоено, Excel можно закрывать - он больше нам не нужен.

Дальше по плану нужно было бы создать файл *.UDL и настроить в нем подключение к нашему файлу C:\Fact.xls. Однако напрямую из файла UDL можно работать только с драйверами OLE DB, а нужного драйвера, к сожалению, нет (Microsoft JET 4.0 OLE DB Provider хочет работать только с файлами MDB). Поэтому делаем еще один подготовительный шаг - создаем источник данных ODBC (поскольку драйвер ODBC для подключения к Excel есть). Первое действие - в Панели управления открываем Administrative Tools (Средства администрирования) и два раза щелкаем по иконке Data Sources (ODBC) (Источники данных ODBC). Откроется окно, аналогичное представленному на рис. 9.9.

Рис. 9.9 Окно управления источниками данных ODBC

В вашем распоряжении - три типа DSN (Data Source Name, то есть источников данных ODBC):

• User DSN - информация об этих источниках данных хранится в части реестра, специфической для пользователя, поэтому эти источники данных доступны только тому пользователю, который их создал;
• System DSN - информация об этих источниках данных хранится в общей части реестра и доступна для всех пользователей на этом компьютере;
• File DSN - информация об этих источниках данных записывается в файл в файловой системе.

Чаще всего используются System DSN - системные источники данных, поэтому переходим на вкладку System DSN и нажимаем на кнопку Add (Создать).

Первое, о чем нас спросят - это о типе драйвера, который мы хотим использовать. Выбираем, конечно, Microsoft Excel Driver и нажимаем на кнопку Finish. Но создание источника данных на этом далеко не кончилось.

На следующем экране нам потребуется:

• в поле Data Source Name ввести имя источника данных. Можно ввести любое имя - главное, чтобы вы его не забыли. Мы введем имя ExcelVolumes;
• нажать на кнопку Select Workbook и выбрать нужную нам рабочую книгу (в нашем случае - C:\Fact.xls);
• нажать на кнопку Options и подумать, будем ли изменять из программы нашу таблицу. Если да, то флажок Read Only нужно снять.

В итоге окно может выглядеть так, как представлено на рис. 9.10.

Рис. 9.10 Настраиваем источник ODBC для подключения к файлу Excel

Осталось нажать два раза на кнопку OK, чтобы закрыть окно создания источника данных ODBC.

В принципе, в коде программы можно написать значение свойства ConnectionString вручную, воспользовавшись документацией по ADO. Выглядеть соответствующая строка, к примеру, может так:

cn.ConnectionString = "Provider=MSDASQL.1;DSN = FactExcel;DBQ=C:\Fact.xls ;"

Но зачем что-то писать руками, когда можно сгенерировать нужное значение автоматически? А сгенерировать можно очень просто:

• так, как описано в предыдущем разделе, создаем файл UDL (можно воспользоваться уже готовым);
• щелкаем по нему два раза мышью, переходим на вкладку Provider и выбираем Microsoft OLE DB Provider for ODBC Drivers;
• переходим на вкладку Connection и в списке Use Data Source Name выбираем созданный нами источник данных ExcelVolumes. Остальные поля можно не заполнять (см. рис. 9.11). Для проверки можно нажать на кнопку Test Connection, а затем - OK.

Рис. 9.11 Настраиваем параметры подключения к созданному источнику ODBC

• последнее действие - открываем созданный нами файл UDL в блокноте, копируем из него строку подключения и используем в нашей программе.

Итоговый код процедуры для подключения к Excel может выглядеть так:

Public Sub ConnectToExcel()

Dim cn As New ADODB.Connection

cn.ConnectionString = "Provider=MSDASQL.1;Data Source=ExcelVolumes"

cn.Open

'Про Recordset мы будем говорить в следующем разделе

'Этот код помещен для наглядной проверки

Dim rs As New ADODB.Recordset

rs.Open "Volumes", cn

MsgBox rs.GetString

End Sub

Чтобы подключиться к файлу Excel, нам потребовалось:

• Создать именованный диапазон в книге Excel;
• Создать источник данных ODBC с именем ExcelVolumes;
• Написать три строки кода начиная с создания объекта Connection до вызова его метода Open.

Получить этот учебник в формате PDF (Acrobat Reader) или DOC (Microsoft Word)